package com.geek.jdbc.work1.dao;


import com.geek.jdbc.work1.entity.Students;
import com.geek.jdbc.work1.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*
 *@Date 2025/11/17
 *@Author wangpeng
 *@Description TODO
 */
public class StudentsDao {
    //查询所有学生信息
    public List<Students> selectAll(){
        List<Students> list = new ArrayList<>();
        //1.获得连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "select * from stus";
            ppst = conn.prepareStatement(sql);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()){
                String id = rs.getString("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                String grade = rs.getString("grade");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                Students students = new Students(id, name, gender, grade, phone, email, birthday);
                list.add(students);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //5.关闭资源
            JDBCUtils.close(ppst,conn,rs);
        }
        return list;
    }

    //按学号查询学生信息
    public Students selectById(String id){
        Students students = null;
        //1.获得连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;

        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "select * from stus where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1,id);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()){
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                String grade = rs.getString("grade");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                students = new Students(id, name, gender, grade, phone, email, birthday);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //5.关闭资源
            JDBCUtils.close(ppst,conn,rs);
        }
        return students;

    }
    //按姓名查询学生信息
    public static List<Students> selectByName(String name){
        List<Students> list = new ArrayList<>();
        //1.获得连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;

        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "select * from stus where name like concat('%',?,'%')";
            ppst = conn.prepareStatement(sql);
               // ⭐⭐⭐ 关键修复：给参数赋值 ⭐⭐⭐
            ppst.setString(1, "%" + name + "%");  // 添加通配符进行模糊匹配
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()){
                String id = rs.getString("id");
                String gender = rs.getString("gender");
                String grade = rs.getString("grade");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                Students students = new Students(id, name, gender, grade, phone, email, birthday);
                list.add(students);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //5.关闭资源
            JDBCUtils.close(ppst,conn,rs);
        }
        return list;
    }
    //修改学生出生日期
    public int updateBirthday(Students students){
        //1.获得连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "update stus set birthday = ? where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setDate(1, (java.sql.Date) students.getBirthday());
            ppst.setString(2,students.getId());
            //3.执行查询操作，获取结果集
            int count = ppst.executeUpdate();
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //4.关闭资源
            JDBCUtils.close(ppst,conn,null);
        }
    }
    //删除学生记录
    public int deleteById(String id){
        //1.获得连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        try {
            //2.预编译SQL语句，获取语句对象
            String sql = "delete from stus where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1,id);
            //3.执行查询操作，获取结果集
            int count = ppst.executeUpdate();
            return count;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //4.关闭资源
            JDBCUtils.close(ppst,conn,null);
        }
    }





}
